package com.iamteer.dbUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.junit.Test;

import com.iamteer.jdbc.Admin;
import com.iamteer.utils.JdbcUtil;

public class App_query {
	private Connection conn;
	
	// 一、查询，自定义结果集封装数据
	@Test
	public void testUpdate() throws Exception{
		String sql = "select * from admin where id=?";
		// 获取连接
		conn = JdbcUtil.getConnection();
		// 创建 DbUtils 核心工具类对象
		QueryRunner qr = new QueryRunner();
		// 查询
		Admin admin = qr.query(conn, sql, new ResultSetHandler<Admin>() {

			@Override
			public Admin handle(ResultSet rs) throws SQLException {
				if (rs.next()) {
					Admin admin = new Admin();
					admin.setId(rs.getInt("id"));
					admin.setUsername(rs.getString("username"));
					admin.setPassword(rs.getString("password"));
					return admin;
				}
				return null;
			}
		},  12);
		
		System.out.println(admin);
		conn.close();
	}
	
	// 二、查询，使用组件提供的结果集对象封装数据
	// 1）查询返回单个对象
	@Test
	public void testQueryOne() throws Exception{
		String sql = "select * from admin where id=?";
		// 获取连接
		conn = JdbcUtil.getConnection();
		// 创建 DbUtils 核心工具类对象
		QueryRunner qr = new QueryRunner();
		
		Admin admin = qr.query(conn, sql, new BeanHandler<Admin>(Admin.class), 12);
		System.out.println(admin);
		conn.close();
	}
	
	// 2）查询返回 list 集合，集合元素是指定的对象
	@Test
	public void testQueryMany() throws Exception{
		String sql = "select * from admin";
		// 获取连接
		conn = JdbcUtil.getConnection();
		// 创建 DbUtils 核心工具类对象
		QueryRunner qr = new QueryRunner();
		
		List<Admin> list = qr.query(conn, sql, new BeanListHandler<Admin>(Admin.class));
		System.out.println(list);
		conn.close();
	}
	
	// 3）ArrayHandler		查询返回结果记录的第一行，封装成对象数组，即返回：Object[]
	// 4）ArrayListHandler	查询返回的每一行都封装成为对象数组，再添加到 list 集合中
	// 5）ScalarHandler		查询返回结果记录的第一行的第一列
	// 6）MapHandler			查询返回结果的第一条记录封装为 map
	@Test
	public void testArray() throws Exception{
		String sql = "select * from admin";
		// 获取连接
		conn = JdbcUtil.getConnection();
		// 创建 DbUtils 核心工具类对象
		QueryRunner qr = new QueryRunner();
		
//		Object[] obj = qr.query(conn, sql, new ArrayHandler());
//		List<Object[]> list = qr.query(conn, sql, new ArrayListHandler());
//		Integer num = qr.query(conn, sql, new ScalarHandler<Integer>());
		
		//sql = "select count(*) from admin";
		//Long num = qr.query(conn, sql, new ScalarHandler<Long>());
		
		Map<String, Object> map = qr.query(conn, sql, new MapHandler());
		
		System.out.println(map);
		conn.close();
	}
}
